<?php
require 'settings.php';

$db_eve = new mysqli( "$eve_host", "$eve_user", "$eve_password", "$eve_db" );
$db_corp = new mysqli( "$corp_host", "$corp_user", "$corp_password", "$corp_db" );

//statements on EVE DB
$get_bpo_categories = $db_eve->prepare("SELECT groupID, groupName FROM invGroups WHERE (categoryID = 9 AND published = 1 AND groupID > 104) ORDER BY groupName");

$get_bpo_category_by_id = $db_eve->prepare("SELECT groupName FROM invGroups WHERE groupID = ?");

$get_bpos_in_category = $db_eve->prepare("SELECT typeID, typeName FROM invTypes WHERE groupID = ? ORDER BY typeName" );

$get_bpo_ids_by_partial_name = $db_eve->prepare("SELECT typeID, typeName from invTypes WHERE typeName LIKE ? ORDER BY groupID, typeName");

//gets all the BPO product details needed for some base calculations. A different query is needed for getting materials
$get_bpo_details = $db_eve->prepare( "SELECT bpo.productTypeID, inv.typeName, bpo.productionTime, bpo.productivityModifier, bpo.materialModifier, bpo.wasteFactor, inv.portionSize FROM invBlueprintTypes AS bpo INNER JOIN $eve_db.invTypes AS inv ON inv.typeID = bpo.productTypeID WHERE bpo.blueprintTypeID = ?" );

//this gets base materials - ME has effect on those
$get_item_base_materials = $db_eve->prepare( "SELECT inv.typeName, mat.materialTypeID, mat.quantity FROM invTypeMaterials AS mat INNER JOIN invTypes AS inv ON  mat.materialTypeID = inv.typeID WHERE mat.typeID = ? ORDER BY inv.groupID, inv.typeName" );

//gets extra materials without skills
$get_bpo_extra_materials = $db_eve->prepare( "SELECT inv.typeName, mat.requiredTypeID, mat.quantity, mat.recycle FROM ramTypeRequirements AS mat INNER JOIN invTypes AS inv ON mat.requiredTypeID = inv.typeID INNER JOIN invGroups AS grp ON grp.groupID = inv.groupID WHERE mat.typeID = ? AND mat.activityID = 1 AND grp.categoryID != 16 ORDER BY inv.groupID, inv.typeName" );

//gets reprocessing results of a base item to see how much of what we need to subtract from base materials
$get_item_reprocess_result = $db_eve->prepare( "SELECT inv.typeName, inv.typeID, mat.quantity FROM invTypeMaterials AS mat INNER JOIN invTypes AS inv ON mat.materialTypeID = inv.typeID WHERE mat.typeID = ?" );

//get BPO ID form item ID
$get_item_bpo_id = $db_eve->prepare( "SELECT blueprintTypeID FROM invBlueprintTypes WHERE productTypeID = ?" );


//statements on corp DB
$get_bpo_by_id = $db_eve->prepare("SELECT typeName FROM invTypes WHERE typeID = ?" );

$add_bpo_to_library = $db_corp->prepare("INSERT INTO library_bpo( ccp_typeID, ME, PE, count ) VALUES (?, ?, ?, ?)");

$show_corp_bpo_library = $db_corp->prepare("SELECT eve.typeName, corp.ccp_typeID, corp.ME, corp.PE, corp.count FROM library_bpo AS corp INNER JOIN $eve_db.invTypes AS eve ON eve.typeID = corp.ccp_typeID ORDER BY eve.groupID, eve.typeName");

$show_corp_prices = $db_corp->prepare("SELECT eve.typeName, corp.ccp_typeID, corp.price, corp.volume FROM prices_materials AS corp INNER JOIN $eve_db.invTypes AS eve ON eve.typeID = corp.ccp_typeID ORDER BY eve.groupID, eve.typeName" );

$update_material_prices = $db_corp->prepare("INSERT INTO prices_materials (ccp_typeID, price) VALUES(?, ?) ON DUPLICATE KEY UPDATE price=VALUES(price)");

$update_table_mod_timestamp = $db_corp->prepare("INSERT INTO pricelists (pricelist_name, pricelist_last_update) VALUES(?, ?) ON DUPLICATE KEY UPDATE pricelist_last_update=VALUES(pricelist_last_update)");

$get_table_mod_timestamp = $db_corp->prepare( "SELECT pricelist_last_update FROM pricelists WHERE pricelist_name=?" );

$get_all_corp_bpos = $db_corp->prepare( "SELECT corp.ccp_typeID, eve.typeName, corp.ME, corp.PE FROM library_bpo AS corp INNER JOIN $eve_db.invTypes AS eve ON eve.typeID = corp.ccp_typeID ORDER BY eve.groupID, eve.typeName" );

$get_all_facilities = $db_corp->prepare( "SELECT facility_id, name, material_multiplier, time_multiplier, installation_cost, per_hour_cost, facility_type FROM facilities" );

$get_all_facilities_names = $db_corp->prepare( "SELECT facility_id, name FROM facilities" );

$get_facility_by_id = $db_corp->prepare( "SELECT name, material_multiplier, time_multiplier, installation_cost, per_hour_cost, facility_type FROM facilities WHERE facility_id = ? ");

$add_facility = $db_corp->prepare( "INSERT INTO facilities(name, material_multiplier, time_multiplier, installation_cost, per_hour_cost, facility_type) VALUES(?, ?, ?, ?, ?, ?)" );

$pricelist_add = $db_corp->prepare( "INSERT INTO pricelists(pricelist_name, pricelist_last_update, markup_over_buildcost, replacement_discount, round_to, production_location_id) VALUES(?, NOW(), ?, ?, ?, ?)" );

$pricelist_get_by_name = $db_corp->prepare( "SELECT pricelist_id FROM pricelists WHERE pricelist_name = ?" );


$get_bpo_levels = $db_corp->prepare( "SELECT ME, PE FROM library_bpo WHERE ccp_typeID = ?" );

$pricelist_insert_data = $db_corp->prepare( "INSERT INTO pricelists_data(pricelist_ID, ccp_bpo_typeID, ccp_product_typeID, build_cost, price_new, price_replacement, materials_volume) VALUES( ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE build_cost=VALUES(build_cost), price_new=VALUES(price_new), price_replacement=VALUES(price_replacement), materials_volume=VALUES(materials_volume)");

$pricelist_get = $db_corp->prepare( "SELECT pl.product_ID, pl.ccp_product_typeID, inv.typeName, pl.build_cost, pl.price_new, pl.price_replacement, pl.materials_volume FROM pricelists_data AS pl INNER JOIN $eve_db.invTypes AS inv ON pl.ccp_product_typeID = inv.typeID WHERE pl.pricelist_ID = ? ORDER BY inv.groupID, inv.TypeName" );

$get_all_pricelists = $db_corp->prepare( "SELECT pl.pricelist_ID, pl.pricelist_name, pl.pricelist_last_update, pl.markup_over_buildcost, pl.replacement_discount, pl.round_to, fac.name FROM pricelists AS pl INNER JOIN facilities AS fac WHERE fac.facility_ID = pl.production_location_id" );

$update_facility = $db_corp->prepare( "UPDATE facilities SET material_multiplier = ?, time_multiplier = ?, installation_cost = ?, per_hour_cost = ? WHERE facility_ID = ?" );

$delete_pricelist = $db_corp->prepare( "DELETE FROM pricelists WHERE pricelist_ID = ?" );
$delete_pricelist_data = $db_corp->prepare( "DELETE FROM pricelists_data WHERE pricelist_ID = ?" );

$pricelist_get_data = $db_corp->prepare( "SELECT pricelist_name, pricelist_last_update, markup_over_buildcost, replacement_discount, round_to, production_location_id FROM pricelists WHERE pricelist_ID = ?" );

$pricelist_update = $db_corp->prepare( "UPDATE pricelists SET pricelist_name = ?, pricelist_last_update = NOW(), markup_over_buildcost = ?, replacement_discount = ?, round_to = ?, production_location_id = ? WHERE pricelist_ID = ?" );

$pricelist_update_data = $db_corp->prepare( "UPDATE pricelists_data SET build_cost = ?, price_new = ?, price_replacement = ?, materials_volume = ? WHERE product_ID = ?" );

$pricelist_materials_data = $db_corp->prepare( "INSERT INTO pricelists_materials(product_ID, material_ID, material_count) VALUES(?, ?, ?) ON DUPLICATE KEY UPDATE material_count = VALUES(material_count)" );

$pricelist_materials_data_get =$db_corp->prepare( "SELECT material_ID, material_count FROM pricelists_materials WHERE product_ID = ?" );

$get_name_by_prod_id = $db_corp->prepare( "SELECT inv.typeName FROM $eve_db.invTypes AS inv INNER JOIN $corp_db.pricelists_data AS pxin ON pxin.ccp_product_typeID = inv.typeID WHERE pxin.product_ID = ?" );

?>
